<?php
include_once 'paginator.class.php';

class db extends PDO {
	protected $error;
	protected $sql;
	protected $bind;
	protected $errorCallbackFunction;
	protected $errorMsgFormat;

    public $rowCount;

	public function __construct($dsn, $user="", $passwd="") {
		$options = array(
			PDO::ATTR_PERSISTENT => true, 
			PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
		);

		try {
			parent::__construct($dsn, $user, $passwd, $options);
		} catch (PDOException $e) {
			$this->error = $e->getMessage();
		}
	}

	protected function debug() {
		if(!empty($this->errorCallbackFunction)) {
			$error = array("Error" => $this->error);
			if(!empty($this->sql))
				$error["SQL Statement"] = $this->sql;
			if(!empty($this->bind))
				$error["Bind Parameters"] = trim(print_r($this->bind, true));

			$backtrace = debug_backtrace();
			if(!empty($backtrace)) {
				foreach($backtrace as $info) {
					if($info["file"] != __FILE__)
						$error["Backtrace"] = $info["file"] . " at line " . $info["line"];	
				}		
			}

			$msg = "";
			if($this->errorMsgFormat == "html") {
				if(!empty($error["Bind Parameters"]))
					$error["Bind Parameters"] = "<pre>" . $error["Bind Parameters"] . "</pre>";
				$css = trim(file_get_contents(dirname(__FILE__) . "/error.css"));
				$msg .= '<style type="text/css">' . "\n" . $css . "\n</style>";
				$msg .= "\n" . '<div class="db-error">' . "\n\t<h3>SQL Error</h3>";
				foreach($error as $key => $val)
					$msg .= "\n\t<label>" . $key . ":</label>" . $val;
				$msg .= "\n\t</div>\n</div>";
			}
			elseif($this->errorMsgFormat == "text") {
				$msg .= "SQL Error\n" . str_repeat("-", 50);
				foreach($error as $key => $val)
					$msg .= "\n\n$key:\n$val";
			}

			$func = $this->errorCallbackFunction;
			$func($msg);
		}
	}

	public function delete($table, $where, $bind="") {
		$sql = "DELETE FROM " . $table . " WHERE " . $where . ";";
		$this->run($sql, $bind);
	}

	private function filter($table, $info) {
		$driver = $this->getAttribute(PDO::ATTR_DRIVER_NAME);
		if($driver == 'sqlite') {
			$sql = "PRAGMA table_info('" . $table . "');";
			$key = "name";
		}
		elseif($driver == 'mysql') {
			$sql = "DESCRIBE " . $table . ";";
			$key = "Field";
		}
		else {	
			$sql = "SELECT column_name FROM information_schema.columns WHERE table_name = '" . $table . "';";
			$key = "column_name";
		}	

		if(false !== ($list = $this->run($sql))) {
			$fields = array();
			foreach($list as $record)
				$fields[] = $record[$key];
			return array_values(array_intersect($fields, array_keys($info)));
		}
		return array();
	}

	protected function cleanup($bind) {
		if(!is_array($bind)) {
			if(!empty($bind))
				$bind = array($bind);
			else
				$bind = array();
		}
		return $bind;
	}

	public function insert($table, $info) {
		$fields = $this->filter($table, $info);
		$sql = "INSERT INTO " . $table . " (" . implode($fields, ", ") . ") VALUES (:" . implode($fields, ", :") . ");";
		$bind = array();
		foreach($fields as $field)
			$bind[":$field"] = $info[$field];
		return $this->run($sql, $bind);
	}

	public function run($sql, $bind="") {
		$this->sql = trim($sql);
		$this->bind = $this->cleanup($bind);
		$this->error = "";

		try {
			$pdostmt = $this->prepare($this->sql);
			if($pdostmt->execute($this->bind) !== false) {
				if(preg_match("/^(" . implode("|", array("select", "describe", "pragma")) . ") /i", $this->sql))
					return $pdostmt->fetchAll(PDO::FETCH_ASSOC);
				elseif(preg_match("/^(" . implode("|", array("delete", "insert", "update")) . ") /i", $this->sql))
					return $pdostmt->rowCount();
			}	
		} catch (PDOException $e) {
			$this->error = $e->getMessage();	
			$this->debug();
			return false;
		}
	}

	public function select($table, $where="", $bind="", $fields="*") {
		$sql = "SELECT " . $fields . " FROM " . $table;
		if(!empty($where))
			$sql .= " WHERE " . $where;
		$sql .= ";";        
		return $this->run($sql, $bind);
	}

	public function setErrorCallbackFunction($errorCallbackFunction, $errorMsgFormat="html") {
		//Variable functions for won't work with language constructs such as echo and print, so these are replaced with print_r.
		if(in_array(strtolower($errorCallbackFunction), array("echo", "print")))
			$errorCallbackFunction = "print_r";

		if(function_exists($errorCallbackFunction)) {
			$this->errorCallbackFunction = $errorCallbackFunction;	
			if(!in_array(strtolower($errorMsgFormat), array("html", "text")))
				$errorMsgFormat = "html";
			$this->errorMsgFormat = $errorMsgFormat;	
		}	
	}

	public function update($table, $info, $where, $bind="") {
		$fields = $this->filter($table, $info);
		$fieldSize = sizeof($fields);

		$sql = "UPDATE " . $table . " SET ";
		for($f = 0; $f < $fieldSize; ++$f) {
			if($f > 0)
				$sql .= ", ";
			$sql .= $fields[$f] . " = :update_" . $fields[$f]; 
		}
		$sql .= " WHERE " . $where . ";";

		$bind = $this->cleanup($bind);
		foreach($fields as $field)
			$bind[":update_$field"] = $info[$field];
		
		return $this->run($sql, $bind);
	}
}	


/**
 * Extension de la clase 
 * 
 * @author ohroot
 *
 */
class myDb extends db{
    protected $isFrontend=true;

    public function setIsFrontend($value){
        return $this->isFrontend == (bool)$value;
    }    
    public function isFrontend(){
        return $this->isFrontend;
    }
    
	public function __construct($dsn, $user="", $passwd="") {
		parent::__construct($dsn, $user, $passwd);
	}	
	
	/**
	 * Ejecuto un statement 
	 * 
	 * Cambio que retorne el id cuando lo crea
	 * 
	 * @see library/db#run()
	 */
	public function run($sql, $bind="") {
		$this->sql = trim($sql);
		$this->bind = $this->cleanup($bind);
		$this->error = "";

		try {
			$pdostmt = $this->prepare($this->sql);
            
            if(!$pdostmt){
                $this->error = $e->getMessage();	
			     $this->debug();
			     return false;
            }	
	
			if($pdostmt->execute($this->bind) !== false) {
				//echo $pdostmt->queryString;
				if(preg_match("/^(" . implode("|", array("select", "describe", "pragma")) . ") /i", $this->sql))
					return $pdostmt->fetchAll(PDO::FETCH_ASSOC);
				elseif(preg_match("/^(" . implode("|", array("delete",  "update")) . ") /i", $this->sql))
					return $pdostmt->rowCount();
				elseif(preg_match("/^(" . implode("|", array("insert")) . ") /i", $this->sql))
					return $this->lastInsertId();				
			}	
		} catch (PDOException $e) {
			$this->error = $e->getMessage();	
			$this->debug();
			return false;
		} catch(Exception $e){
			$this->error = $e->getMessage();	
			$this->debug();
			return false;			
		}
	}
	
	
	/**
	 * Anexamos el paginador al select 
	 * 
	 * @see library/db#select()
	 */
	public function select($table, $where="", $bind="", $fields="*",$orderby='',$pnCount=false,$pnOffset=false) {
		$sql = "SELECT " . $fields . " FROM " . $table;
		if(!empty($where)){
            if( is_array($where))
                $where = join(' and ', $where);
			$sql .= " WHERE " . $where;        
        }

		

		if(!empty($orderby)){
			$sql .= " order by $orderby ";
		}	
			
		if(!empty($pnCount)){
			$pnOffset = (int)$pnOffset;
			$pnCount = (int)$pnCount;
			
			$sql .= " limit {$pnOffset}, {$pnCount} ";
		}else{
			$sql .= $this->paginator($sql,$fields,$bind);
		}	
			
		$sql .= ";";
		
// 		if($this->debugging)echo "<pre>$sql</pre>";
	
		return $this->run($sql, $bind);
	}	
	// ------------ new method ------------------------------------------------
	
	public function rawSearch($psQuery,$psTable,$paQuery=array(),$paFilter=array(),$paOrder=array(),$pnCount='',$pnOffset=''){
		
		if($this->isFrontend()){
			$paFilter['f_enabled']=1; // todo lo que vaya al frontend que este habilitado
		}
		
		$laFields = $this->getFields($psTable);
		
		$fields = substr($psQuery,0,stripos($psQuery,"from"));
		$fields = str_ireplace("select", "", $fields);
		
		$where_filter = $this->processFilter($psTable,$paFilter,$laFields);
		$order_by = $this->processOrder($psTable,$paOrder,$laFields);
		$where_query = $this->processQuery($psTable,$paQuery,$laFields);
//print_r(array($where_filter,$order_by,$where_query));
		if($order_by)
			$lsOrder = join(", ",$order_by);
		
		if($where_filter)
			$where[] = "(".join(" and ",$where_filter).")";
			
		if($where_query)
			$where[] = "(".join(" or ",$where_query).")";
		
		if($where)
			$where = "(".join(" and ",$where).")";

		$sql = $psQuery;
		if($where){
			$sql .= (stripos($sql,'WHERE')?' and ':' where ' ) . $where;
		}	
			
			
			
		if(!empty($lsOrder)){
			$sql .= " order by $lsOrder ";
		}	
			
		if(!empty($pnCount)){
			$pnOffset = (int)$pnOffset;
			$pnCount = (int)$pnCount;
			
			// $sql .= " limit {$pnOffset}, {$pnCount} ";
			
			$_GET_page = $_GET['page'];
			$_GET_ipp = $_GET['ipp'];
			
			$_GET['page'] = $pnOffset;
			$_GET['ipp'] = $pnCount;
			
			$sql .= $this->paginator($sql,$fields,$bind);
			
			$_GET['page'] = $_GET_page;
			$_GET['ipp'] = $_GET_ipp;			
			
			
		}else{
			$sql .= $this->paginator($sql,$fields,$bind);
		}	
			
		$sql .= ";";
//echo $sql;		
		//if($this->debugging) echo "<pre>$sql</pre>";
		
		$result = $this->query($sql);
		return $result;
		
	}
	
	
	/**
	 * Realiza las busquedas genericas para una entidad determinana
	 * 
	 * 
	 * @param mixed $paQuery
	 * @param mixed $paFilter
	 * @param mixed $paOrder
	 * @param mixed $pnCount
	 * @param mixed $pnOffset
	 * @return mixed Array con los resultados de las busquedas
	 */
	public function search($psTable,$paQuery=array(),$paFilter=array(),$paOrder=array(),$pnCount='',$pnOffset=''){
		
		if($this->isFrontend()){
			$paFilter['f_enabled']=1; // todo lo que vaya al frontend que este habilitado
		}
				
		$laFields = $this->getFields($psTable);
		$lsFields = "`".join('`,`',$laFields)."`";

		$where_filter = $this->processFilter($psTable,$paFilter,$laFields);
		$order_by = $this->processOrder($psTable,$paOrder,$laFields);
		$where_query = $this->processQuery($psTable,$paQuery,$laFields);

		if($order_by)
			$lsOrder = join(", ",$order_by);
		
		if($where_filter)
			$where[] = "(".join(" and ",$where_filter).")";
			
		if($where_query)
			$where[] = "(".join(" or ",$where_query).")";
		
		if($where)
			$where = "(".join(" and ",$where).")";

		
		//$this->debugging=true;

		$result = $this->select($psTable,$where,$bind,$lsFields,$lsOrder,$pnCount,$pnOffset);
		return $result;
		
	}

	/**
	 * Procesa los query para una entidad
	 * Son todos AND
	 * 
	 * $paFilter = array(
	 * 	'q_nombre' => 'mario',
	 * 	'q_apellido' => 'garcia'
	 * )
	 * 
	 * $paFields = array('id','nombre','apellido','direccion')
	 * 
	 * @param string $psTable nombre de la tabla
	 * @param mixed $paFilter
	 * @param mixed $paFields
	 * @return mixed
	 */
	protected function processQuery($psTable,&$paQuery,$paFields){
		$where = array();
		
		if($paQuery['q']){
			// busco en todos los campos
			foreach($paFields as $f){
				$where[]=" {$psTable}.`{$f}` like '%{$paQuery['q']}%' ";	
			}
		}else{
			// busco solo en los campos especificados
			foreach($paQuery as $k => $v){
				if( stripos($k,'q_')!==false && $v){
					$f = substr($k,2);
					$where[]=" {$psTable}.`{$f}` like '%{$v}%' ";
				}
			}			
		}
		
foreach($paQuery as $k => $v){		
    if( stripos($k,'qe_')!==false && $v){
	$f = substr($k,3);
	$where[]=" $f like '%{$v}%' ";				    
    }
}
		
		return $where;
		
	
		
	}	
	
	/**
	 * Procesa los filtros para una entidad
	 * Son todos AND
	 * 
	 * $paFilter = array(
	 * 	'f_nombre' => 'mario',
	 * 	'f_apellido' => 'garcia'
	 * )
	 * 
	 * $paFields = array('id','nombre','apellido','direccion')
	 * 
	 * @param string $psTable nombre de la tabla
	 * @param mixed $paFilter
	 * @param mixed $paFields
	 * @return mixed
	 */
	protected function processFilter($psTable,&$paFilter,$paFields){
		$where = array();
		foreach($paFilter as $k => $v){
			if( stripos($k,'f_')!==false && $v){
				
				if(stripos($k,'f_not_')!==false){
					$f = substr($k,6);
					$where[]=" {$psTable}.`{$f}` <> '$v' ";
				}else{
					$f = substr($k,2);
					$where[]=" {$psTable}.`{$f}` = '$v' ";
					
				}
			}else if( stripos($k,'fe_')!==false && $v){
					$f = substr($k,3);
					$where[]=" {$f} = '$v' ";	    
			}
		}	
		return $where;
	}
	
	/**
	 * Procesa los order para una entidad
	 * 
	 * $paFilter = array(
	 * 	'o_nombre' => 'desc',
	 * 	'o_apellido' => 'asc' 
	 * )
	 * 
	 * $paFields = array('id','nombre','apellido','direccion')
	 * 
	 * @param string $psTable nombre de la tabla
	 * @param mixed $paFilter
	 * @param mixed $paFields
	 * @return mixed
	 */
	protected function processOrder($psTable,&$paOrder,$paFields){
		$orderby = array();
		foreach($paOrder as $k => $v){
			if( stripos($k,'o_')!==false && $v){
				$f = substr($k,2);
				$orderby[]=" {$psTable}.`{$f}`  $v ";
			}
		}	
		
		if(empty($orderby)){
// 			$orderby[]=" {$psTable}.orden  ";
		}
		
		return $orderby;
	}
	
	/**
	 * Retorna los campos de una tabla
	 * 
	 * TODO: podemos buscar mas info de los campos
	 * 
	 * @param string $table Nombre de la tabla a listar campos
	 * @return mixed Array con los campos de la tabla
	 */
	private function getFields($table) {
		$driver = $this->getAttribute(PDO::ATTR_DRIVER_NAME);
		if($driver == 'sqlite') {
			$sql = "PRAGMA table_info('" . $table . "');";
			$key = "name";
		}
		elseif($driver == 'mysql') {
			$sql = "DESCRIBE " . $table . ";";
			$key = "Field";
		}
		else {	
			$sql = "SELECT column_name FROM information_schema.columns WHERE table_name = '" . $table . "';";
			$key = "column_name";
		}	

		if(false !== ($list = $this->run($sql))) {			
			$fields = array();
			foreach($list as $record)
				$fields[] = $record[$key];
			return $fields;
		}
		
		return array();
	}	
	
	// -------------------
	
	/**
	 * 
	 * @param string $sql
	 * @param string $fields
	 * @param mixed $bind
	 * @return string Limit para la consulta
	 */
	private function paginator($sql,$fields,$bind){
		
		$sql_count = str_ireplace(trim($fields)," count(*) as total ",$sql); 
		
		$result_count = $this->run($sql_count, $bind);
		$this->rowCount = $result_count[0]['total'];
				
		$this->paginator = new Paginator;  
		$this->paginator->items_total = $this->rowCount;
		$this->paginator->paginate();
	
		return $this->paginator->limit;		
	}

	public function display_pages(){
		return $this->paginator->display_pages();
	}
}
